package com.ftg.learn.jdbc.chapter01.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson2.JSON;
import com.mysql.cj.jdbc.Driver;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.annotation.Annotation;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;
import java.util.function.Supplier;

/**
 * DAo :  data access object :
 * 高工
 * 设计 人员
 * 前台对接问题 :
 * 轻量协议
 * 重量协议
 * 字符串 ： Object -- json
 * json格式  ：
 * {key:value,key:value。。。}  jsonobject
 * []  josnArray
 * [ {key:value,key:value。。。}]
 *
 * @author kangjx
 * @createTime 2022/9/23
 */
@SuppressWarnings("all")
public class DaoUtil<T> {

    /**
     * 默认值 ： 后期要做分离
     */
    private final static String DEFAULT_USERNAME = "root";
    private final static String DEFAULT_PASSWORD = "Love0913@kjx";
    private final static String DEFAULT_URL = "jdbc:mysql://localhost:3306/learn07?serverTimezone=UTC";

    private final String username;
    private final String password;
    private final String url;

    /**
     * 采用默认的信息连连数据库
     */
    public DaoUtil() {
        this.username = DEFAULT_USERNAME;
        this.password = DEFAULT_PASSWORD;
        this.url = DEFAULT_URL;
    }

    /**
     * 给定值
     *
     * @param username 用户名
     * @param password 密码
     * @param url      地址
     */
    public DaoUtil(String username, String password, String url) {
        this.username = username;
        this.password = password;
        this.url = url;
    }

    /**
     * 这是用阿里巴巴druid连接池获得的会话
     * @return
     * @throws SQLException
     */
    private DruidPooledConnection init() throws SQLException {
        DruidDataSource ds = new DruidDataSource();
        ds.setUsername(this.username);
        ds.setPassword(this.password);
        ds.setUrl(this.url);
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");

        ds.setMaxActive(20);
        ds.setInitialSize(1);
        ds.setMinIdle(1);
        ds.setMaxWait(6000);
        return ds.getConnection();
    }


    /**
     * 用来给sql？ 赋值的方法s
     *
     * @param stmt 预编译 sql
     * @param para 参数
     * @throws SQLException
     */
    private void setParameterWithSql(PreparedStatement stmt, Object... para) throws SQLException {
        if (Objects.nonNull(para)) {
            for (int i = 0; i < para.length; i++) {
                stmt.setObject(i + 1, para[i]);
            }
        }
    }

    /**
     * 获得当前类的泛型
     *
     * @return Clazz
     */
    private Class getCurrClassParameterType() {
        Type type = this.getClass().getGenericSuperclass();
        ParameterizedType ptClass = (ParameterizedType) this.getClass().getGenericSuperclass();
        Type[] ts = ptClass.getActualTypeArguments();
        if (Objects.isNull(ts) || ts.length <= 0) {
            throw new RuntimeException("请在使用的时候传递参数");
        }
        Class clazz = (Class) ptClass.getActualTypeArguments()[0];
        return clazz;
    }

    /**
     * 完成rs转换成 object - 泛型的过程，并把最终结果存储 list里面
     *
     * @param rs    结果集
     * @param clazz 泛型
     * @return
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IntrospectionException
     * @throws InvocationTargetException
     */
    @SuppressWarnings("all")
    private List<T> converClassToT(ResultSet rs, Class clazz) throws SQLException, InstantiationException, IllegalAccessException, IntrospectionException, InvocationTargetException {
        List<T> objs = new ArrayList<>(10);
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()) {
            Object obj = clazz.newInstance();
            for (int i = 0; i < rsmd.getColumnCount(); i++) {

                String columnName = rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT);
                String showName = columnName;

                Field[] fs = clazz.getDeclaredFields();
                for (int i1 = 0; i1 < fs.length; i1++) {
                    Field field = fs[i1];
                    if (field.getAnnotation(MyId.class) != null || field.getAnnotation(MyColumn.class) != null) {
                        MyId id = field.getAnnotation(MyId.class);
                        MyColumn column = field.getAnnotation(MyColumn.class);
                        String temValue = "";
                        if (id != null) {
                            temValue = id.value();
                        }

                        if (column != null) {
                            temValue = column.value();
                        }
                        if (temValue.equals(columnName)) {
                            showName = field.getName();
                        }
                    }

                    PropertyDescriptor pd = new PropertyDescriptor(showName, clazz);
                    Method writeMethod = pd.getWriteMethod();
                    String columnType = rsmd.getColumnTypeName(i + 1);
                    switch (columnType) {
                        case "INT":
                            writeMethod.invoke(obj, rs.getInt(i + 1));
                            break;
                        case "VARCHAR":
                            writeMethod.invoke(obj, rs.getString(i + 1));
                            break;
                        default:
                            writeMethod.invoke(obj, rs.getObject(i + 1));
                    }
                }
            }
            objs.add((T) obj);
        }
        return objs;
    }

    /**
     * 查询 方法 ，用来朅掉类型转换
     *
     * @param sql
     * @param para
     * @return
     */
    public List<T> queryForClass(String sql, Object... para) {
        //1 ： 如何获得当前类的泛型 (难点)
        Class clazz = this.getCurrClassParameterType();
        try (
                Connection conn = this.init();
                PreparedStatement stmt = conn.prepareStatement(sql);
        ) {
            this.setParameterWithSql(stmt, para);
            ResultSet rs = stmt.executeQuery();
            return this.converClassToT(rs, clazz);
        } catch (SQLException | InstantiationException | IllegalAccessException | IntrospectionException | InvocationTargetException throwables) {
            throwables.printStackTrace();
        }
        return new ArrayList<>(0);
    }

    /**
     * 为了解决 ，有可能是多表查询时返回对象使用
     *
     * @param c    返回类型 vo
     * @param sql  sql
     * @param para 参数
     * @return
     */
    public List<Object> queryForClass(Class c, String sql, Object... para) {
        if (Objects.nonNull(c)) {
            List<Object> objs = new ArrayList<>(10);
            try (
                    Connection conn = this.init();
                    PreparedStatement stmt = conn.prepareStatement(sql);
            ) {
                this.setParameterWithSql(stmt, para);
                ResultSet rs = stmt.executeQuery();
                return (List<Object>) this.converClassToT(rs, c);
            } catch (SQLException | InstantiationException | IllegalAccessException | IntrospectionException | InvocationTargetException throwables) {
                throwables.printStackTrace();
            }
            return objs;
        }
        return null;
    }


    /**
     * 返回值
     * 预编译sql
     * select eno,ename,job,mgr from emp where eno = ?
     *
     * @param sql
     */
    public List<Map<String, Object>> queryForList(String sql, Object... para) {
        List<Map<String, Object>> list = new ArrayList<>();
        try (
                Connection conn = this.init();
                PreparedStatement stmt = conn.prepareStatement(sql);
        ) {
            this.setParameterWithSql(stmt, para);
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>(5);
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(Locale.ROOT), rs.getObject(i + 1));
                }
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 转换函数
     *
     * @param obj
     * @return
     */
    private String queryForJson(Object obj) {
        return JSON.toJSONString(obj);
    }

    /**
     * 把queryForList 这个方法 返回 结果转换成json
     *
     * @param sql 语句
     * @param obj 参数
     * @return
     */
    public String queryForListToJson(String sql, Object... obj) {
        return queryForJson(this.queryForList(sql, obj));
    }

    /**
     * 把queryForClass的结果转换成为json
     *
     * @param sql 语句
     * @param obj 参数
     * @return
     */
    public String queryForClassToJson(String sql, Object... obj) {
        return queryForJson(this.queryForClass(sql, obj));
    }

    /**
     * 把指定的class类型转换成为json
     *
     * @param c   类型
     * @param sql 语句
     * @param obj 参数
     * @return
     */
    public String queryForPojoToJson(Class c, String sql, Object... obj) {
        return queryForJson(this.queryForClass(c, sql, obj));
    }


    /**********************************下面的代码来完成添加修改删除操作***********************************/

    /**
     * 处理 返回结果为数字情况  ·添加修改删除 「
     * dml [insert,update,delete] ddl[create drop]
     *
     * @return
     */
    public int doExecuteUpdate(String sql, Object... obj) {
        try (
                Connection conn = this.init();
                PreparedStatement stmt = conn.prepareStatement(sql);
        ) {

            this.setParameterWithSql(stmt, obj);
            return stmt.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    /**
     * 用来给添加修改删除方法使用，获得表名
     * 表名为 ： 注解里面的属性或者类名
     *
     * @param t1 传递的实体
     * @return
     */
    private String getTableName(T t1) {
        Class<Annotation> c = (Class<Annotation>) t1.getClass();
        return this.getTableName(c);
    }

    /**
     * 重载一下用来处理只知道类名，或者泛型的时候
     *
     * @param c
     * @return
     */
    private String getTableName(Class<Annotation> c) {
        //生成的表名有两种可能，一种为 ： 类名   类的注解名称
        MyTableName tableName = c.getAnnotation(MyTableName.class);
        return tableName == null ? c.getSimpleName().toLowerCase(Locale.ROOT) : tableName.value();
    }

    /**
     * 这是我们添加方法生成相关信息的核心方法
     * 用来帮我们提供我们生成insert into 表名(列名) values(?) 所要用到的所有信息
     *
     * @param fs javaBean当中的所有字段信息
     * @param t1 操作的对象
     * @return map.put(" columns ", columns.substring ( 0, columns.length () - 1)); 列名
     * map.put("vals",vals.substring(0,vals.length() - 1));  ？
     * map.put("param",objs.toArray());   参数
     * map.put("tableName",getTableName(t1));  表名
     */
    private Map<String, Object> createSqlInfo(Field[] fs, T t1) {
        Map<String, Object> map = new HashMap<>();

        List<Object> objs = new ArrayList<>();
        //把类里面的所有字段当成是表里面的列名
        StringBuilder columns = new StringBuilder(" ");
        StringBuilder vals = new StringBuilder(" ");

        int index = 0;
        for (int i = 0; i < fs.length; i++) {
            try {
                PropertyDescriptor pd = new PropertyDescriptor(fs[i].getName(), t1.getClass());
                Method readMethod = pd.getReadMethod();
                //拿到了getName(）这个字段的getter方法的返回值 obj
                Object obj = readMethod.invoke(t1);
                if (obj != null) {
                    columns.append(fs[i].getName().toLowerCase(Locale.ROOT)).append(",");
                    vals.append("?").append(",");
                    objs.add(readMethod.invoke(t1));
                    index++;
                }
            } catch (IntrospectionException | IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        if (index == 0) {
            throw new RuntimeException("对不起没有传递相关参数或者参数列表为空");
        }
        map.put("columns", columns.substring(0, columns.length() - 1));
        map.put("vals", vals.substring(0, vals.length() - 1));
        map.put("param", objs.toArray());
        map.put("tableName", getTableName(t1));
        return map;
    }

    /**
     * 优化调用方式
     *
     * @param map 处理以后的信息
     * @return
     */
    private String createSaveSql(Map<String, Object> map) {
        return this.createSaveSql(map.get("columns").toString(), map.get("vals").toString(), map.get("tableName").toString());
    }

    /**
     * 为添加方法生成sql语句
     *
     * @param columns   列名信息
     * @param vals      ？ 信息
     * @param tableName 表名信息
     * @return
     */
    private String createSaveSql(String columns, String vals, String tableName) {
        StringBuilder stringBuilder = new StringBuilder(" insert into ");
        stringBuilder.append(tableName).append(" ( ").append(columns).append(" ) ").append(" values (").append(vals).append(")");
        return stringBuilder.toString();
    }

    /**
     * 是否可以通过t1生成添加语句呢？ 要与注解连用  @MyTableName 连用
     *
     * @param t1 这就是我们的一个实体
     * @return
     */
    public int saveObject(T t1) {
        Map<String, Object> sqlInfo = this.createSqlInfo(t1.getClass().getDeclaredFields(), t1);
        String sql = this.createSaveSql(sqlInfo);
        Object[] objs = (Object[]) sqlInfo.get("param");
        return this.doExecuteUpdate(sql, objs);
    }

    /**********************************下面的代码来完成添加修改删除操作***********************************/
    /**
     * 根据主键进行删除一条操作
     *
     * @param dept 主键
     * @return
     */
    public int deleteById(Serializable... dept) {
        return this.doExecuteUpdate(this.createDeleteSqlById(), dept);
    }

    /**
     * 用来处理生成按照主键删除的sql语句
     *
     * @return
     */
    private String createDeleteSqlById() {
        Class c = this.getCurrClassParameterType();
        Field[] fs = c.getDeclaredFields();
        String where = this.createWhereById(fs, c.getName());
        StringBuilder sql = new StringBuilder("delete from ");
        sql.append(this.getTableName(c)).append(where);
        return sql.toString();
    }

    /**
     * 用来给修改，删除生成where条件的sql
     *
     * @param fs      泛型里面的所有列
     * @param tipPath 用来提示路径
     * @return
     */
    private String createWhereById(Field[] fs, String tipPath) {
        StringBuilder keys = new StringBuilder(" where ");
        int index = 0;
        for (int i = 0; i < fs.length; i++) {
            Field key = fs[i];
            MyId id = key.getAnnotation(MyId.class);
            if (id != null) {
                //是主键
                String temp = id.value().equals("") ? key.getName() : id.value();
                keys.append(temp.toLowerCase(Locale.ROOT)).append(" = ").append(" ? ").append(" and ");
                index++;
            }
        }
        if (index == 0) {
            throw new RuntimeException("请为你的映射类" + tipPath + "添加主键映射 @MyId");
        }
        return keys.substring(0, keys.lastIndexOf(" and "));
    }

    /**
     * 通过封装lambda表达式来完成删除操作
     * 按指定条件进行删除
     *
     * @return
     */
    public int deleteByPara(Supplier<String> where, Object... obj) {
        return this.doExecuteUpdate(this.createDeleteSqlByPara(where), obj);
    }

    /**
     * 通过供给型函数式接口完成删除sql生成操作
     *
     * @param where 供给型函数式接口
     * @return
     */
    private String createDeleteSqlByPara(Supplier<String> where) {
        StringBuilder sql = new StringBuilder("delete from ");
        sql.append(this.getTableName(this.getCurrClassParameterType()))
                .append(" ")
                .append(where.get());
        return sql.toString();
    }

    /************通用update**************/
    // 生成sql 调用

    /**
     * 给修改生成  set columne=? 语句
     *
     * @param fs 字段
     * @param t1 实体
     * @return
     */
    private String createUpdateSets(Field[] fs, T t1) {
        String sets = "";
        int index = 0;
        for (int i = 0; i < fs.length; i++) {
            try {
                PropertyDescriptor pd = new PropertyDescriptor(fs[i].getName(), t1.getClass());
                Method readMethod = pd.getReadMethod();
                //拿到了getName(）这个字段的getter方法的返回值 obj
                Object obj = readMethod.invoke(t1);
                Field key = fs[i];
                MyId id = key.getAnnotation(MyId.class);
                if (id == null) {
                    MyColumn cols = key.getAnnotation(MyColumn.class);
                    String temp = id == null ? fs[i].getName().toLowerCase(Locale.ROOT) : cols.value();
                    if (obj != null) {
                        sets += temp + " = ? ,";
                        index++;
                    }
                }
            } catch (IntrospectionException | IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        if (index == 0) {
            throw new RuntimeException("没有列要修改!Dept @myid  @Mycolumn");
        }
        return sets.substring(0, sets.lastIndexOf(","));
    }

    /**
     * 生成修改的sql
     *
     * @param t1
     * @return
     */
    public String createModifySql(T t1) {
        StringBuilder sql = new StringBuilder("update ");
        sql.append(this.getTableName(t1))
                .append(" set ")
                .append(createUpdateSets(t1.getClass().getDeclaredFields(), t1))
                .append(createWhereById(t1.getClass().getDeclaredFields(), t1.getClass().getName()));
        return sql.toString();
    }

    public Object[] updateParameter(T t1) {
        List<Object> setParameter = new ArrayList<>();
        List<Object> whereParameter = new ArrayList<>();
        Class c1 = t1.getClass();
        Field[] fs = c1.getDeclaredFields();
        int setIndex = 0;
        int whereIndex = 0;
        for (int i = 0; i < fs.length; i++) {
            try {
                PropertyDescriptor pd = new PropertyDescriptor(fs[i].getName(), t1.getClass());
                Method readMethod = pd.getReadMethod();
                //拿到了getName(）这个字段的getter方法的返回值 obj
                Object obj = readMethod.invoke(t1);
                Field key = fs[i];
                MyId id = key.getAnnotation(MyId.class);
                if (id != null) {
                    whereParameter.add(obj);
                    whereIndex++;
                } else {
                    setParameter.add(obj);
                    setIndex++;
                }

            } catch (IntrospectionException | IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            }
        }

        if (whereIndex == 0) {
            throw new RuntimeException("对不起当前的" + t1.getClass().getName() + "实体类当中没有声明主键!@MyId");
        }

        if (setIndex == 0) {
            throw new RuntimeException("当前参数" + t1.getClass().getSimpleName() + "参数里面没有传递要修改的项！！");
        }
        setParameter.addAll(whereParameter);
        return setParameter.toArray();
    }

    /**
     * 实体修改操作
     *
     * @param t1 实体
     * @return
     */
    public int modify(T t1) {
        return this.doExecuteUpdate(createModifySql(t1), this.updateParameter(t1));
    }

    /**
     * jdbc底层就把事务做了隔离
     * commit 数据同步到硬盘里面了
     * commit rollback 实现了 原子性  一致性
     * 用来处理多条sql 包含tcl处理的方法
      * @param sqls  多条sql以数据传递
     * @param para  多个参数，每一组参数对应一条sql
     * @return  boolean
     */
    public boolean doBathDml(String[] sqls,Object[]...para){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = this.init();
            //自动提交
            if(conn.getAutoCommit()){
                //改成手动提交
                conn.setAutoCommit(false);
            }
            for (int i = 0; i < sqls.length; i++) {
                String sql = sqls[i];
                stmt = conn.prepareStatement(sql);
                //赋值
                if(para[i] != null){
                    for (int i1 = 0; i1 < para[i].length; i1++) {
                        stmt.setObject(i1 + 1,para[i][i1]);
                    }
                }
                stmt.executeUpdate();
            }
            //表示都执行完了，并且没有问题，那么我们要提交了
            conn.commit();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            if(null != conn){
                try {
                    conn.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return false;
        }finally{
            if(null != conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != stmt){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}
